package com.yitong.bmd.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.yitong.bmd.domain.BmdEntity;
import com.yitong.bmd.domain.WhiteInfo;

@Repository
public class BmdManagerDaoImpl implements BmdManagerDao{
	
	@Autowired
	@Qualifier("secondJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

	
	@Override
	public List<BmdEntity> getWhiteUserList(String qynbzh, String ygmc,String sfxh, String monthsalary, String months, String cxlb,int curpageno, int pagesize) {
		List<Object> list = new ArrayList<Object>(); 
		if(!sfxh.equals(""))list.add(sfxh);
		if(!ygmc.equals(""))list.add(ygmc);
		if(!qynbzh.equals(""))list.add(qynbzh);
		if(!monthsalary.equals(""))list.add(monthsalary);
		if(!months.equals(""))list.add(months);
		if(!cxlb.equals(""))list.add(cxlb);
		list.add(curpageno*pagesize);
		list.add((curpageno-1)*pagesize);
		
		List<BmdEntity> list1 = jdbcTemplate.query(
			"select * from ("+
				"select D.*, rownum rownum_ from ("+
					"select * from ("+
						"select B.ent_cust_id,B.ent_name,B.cust_idt_no,B.cust_name,(case when A.cust_idt_no is null then 0 else 1 end) iswhite,B.proxy_org_id "+ 
						"from crm_dm.per_proxy_customer_roster B "+
						"left join crm_fact.per_credit_record_whitelist A on A.cust_idt_no=B.cust_idt_no where 1=1 "+
						(sfxh.equals("")?"":"and B.cust_idt_no=? ")+
						(ygmc.equals("")?"":"and B.cust_name like '%?%' ")+
						(qynbzh.equals("")?"":"and B.ent_cust_id in (select B.cust_id from crm_init.sop_mir_unitsaveacc A,crm_init.cust_info_all B where A.customerid=B.Cust_No and A.accountno=?) ")+
						(monthsalary.equals("")?"":"and B.month_salary >= ? ")+
						(months.equals("")?"":"and B.cust_proxy_months >= ?")+
					") C where 1=1 "+ (cxlb.equals("")?"":"and C.iswhite = ? ")+
				") D where rownum <= ? "+
			") where rownum_ > ?",
			list.toArray(),
			new TestRowMapper()
	    );
		
		return list1;
	}

	@Override
	public Long getTotalItemCounts(String qynbzh, String ygmc, String sfxh,String monthsalary, String months, String cxlb) {
		List<Object> list = new ArrayList<Object>(); 
		if(!sfxh.equals(""))list.add(sfxh);
		if(!ygmc.equals(""))list.add(ygmc);
		if(!qynbzh.equals(""))list.add(qynbzh);
		if(!monthsalary.equals(""))list.add(monthsalary);
		if(!months.equals(""))list.add(months);
		if(!cxlb.equals(""))list.add(cxlb);
		
		Object o = jdbcTemplate.queryForObject(
			"select count(*) from ("+
				"select B.ent_cust_id,B.ent_name,B.cust_idt_no,B.cust_name,(case when A.cust_idt_no is null then 0 else 1 end) iswhite,B.proxy_org_id "+ 
				"from crm_dm.per_proxy_customer_roster B "+
				"left join crm_fact.per_credit_record_whitelist A on A.cust_idt_no=B.cust_idt_no where 1=1 "+
				(sfxh.equals("")?"":"and B.cust_idt_no=? ")+
				(ygmc.equals("")?"":"and B.cust_name like '%?%' ")+
				(qynbzh.equals("")?"":"and B.ent_cust_id in (select B.cust_id from crm_init.sop_mir_unitsaveacc A,crm_init.cust_info_all B where A.customerid=B.Cust_No and A.accountno=?) ")+
				(monthsalary.equals("")?"":"and B.month_salary >= ? ")+
				(months.equals("")?"":"and B.cust_proxy_months >= ?")+
			") C where 1=1 "+ 
			(cxlb.equals("")?"":"and C.iswhite = ? "),
			list.toArray(),
			String.class
	    );
		
		return Long.parseLong(o.toString());
	}

	@Override
	public String getEntLvl(String ent_cust_id) {
		String str = jdbcTemplate.queryForObject("select A.ent_lvl from crm_fact.per_credit_record_whitelist A where A.ent_cust_id=?",new Object[]{ent_cust_id}, String.class);
		if(str==null){
			return "";
		}else{
			return str;
		}
	}

	@Override
	public String getWhiterBySfz(String cust_idt_no) {
		String str = jdbcTemplate.queryForObject("select count(1) from crm_fact.per_credit_record_whitelist A where A.cust_idt_no=?",new Object[]{cust_idt_no}, String.class);
		if(str==null){
			return "false";
		}else{
			return "true";
		}
	}

	@Override
	public String insertWhiter(final WhiteInfo w) {
		String sql = "insert into crm_fact.per_credit_record_whitelist(ent_cust_id,cust_idt_no,ent_lvl,input_date,acc_org_id) values(?,?,?,sysdate,?)";
		int count = jdbcTemplate.update(sql,new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt)throws SQLException {
				pstmt.setString(1, w.getEnt_cust_id());
				pstmt.setString(2, w.getCust_idt_no());
				pstmt.setString(3, w.getEnt_lvl());
				pstmt.setString(4, w.getAcc_org_id());
			}
		});
		if(count>0){
			return "true";
		}else{
			return "false";
		}
	}

	@Override
	public String deleteWhiter(final WhiteInfo w) {
		String sql = "delete from crm_fact.per_credit_record_whitelist where cust_idt_no=?";
		int count = jdbcTemplate.update(sql,new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement pstmt)throws SQLException {
				pstmt.setString(1, w.getCust_idt_no());
			}
		});
		if(count>0){
			return "true";
		}else{
			return "false";
		}
	}

}

class TestRowMapper implements RowMapper<BmdEntity>{
	@Override
	public BmdEntity mapRow(ResultSet rs, int rowNum) throws SQLException {
		BmdEntity t = new BmdEntity();
		t.setEnt_name(rs.getString("ent_name"));
		t.setEnt_cust_id(rs.getString("ent_cust_id"));
		t.setCust_name(rs.getString("cust_name"));
		t.setCust_idt_no(rs.getString("cust_idt_no"));
		t.setIswhite(rs.getString("iswhite"));
		t.setProxy_org_id(rs.getString("proxy_org_id"));
		return t;
	}
}